<?php
/**
 * 团队系统数据库设置脚本
 * 添加团队相关字段和表
 */

require_once __DIR__ . '/config/database.php';

echo "开始设置团队系统...\n";

try {
    // 1. 为users表添加邀请人字段
    echo "添加邀请人字段到users表...\n";
    $pdo->exec("ALTER TABLE users ADD COLUMN inviter_id INT NULL AFTER balance");
    $pdo->exec("ALTER TABLE users ADD COLUMN invite_code VARCHAR(20) UNIQUE NULL AFTER inviter_id");
    $pdo->exec("ALTER TABLE users ADD COLUMN total_commission DECIMAL(10,2) DEFAULT 0.00 AFTER invite_code");
    $pdo->exec("ALTER TABLE users ADD INDEX idx_inviter_id (inviter_id)");
    $pdo->exec("ALTER TABLE users ADD INDEX idx_invite_code (invite_code)");
    
    // 2. 创建团队手续费分成记录表
    echo "创建团队手续费分成记录表...\n";
    $pdo->exec("CREATE TABLE team_commissions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        inviter_id INT NOT NULL,
        user_id INT NOT NULL,
        transaction_id INT NOT NULL,
        commission_rate DECIMAL(5,4) DEFAULT 0.4000,
        commission_amount DECIMAL(10,2) NOT NULL,
        original_fee DECIMAL(10,2) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_inviter_id (inviter_id),
        INDEX idx_user_id (user_id),
        INDEX idx_transaction_id (transaction_id),
        INDEX idx_created_at (created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    
    // 3. 为现有用户生成邀请码
    echo "为现有用户生成邀请码...\n";
    $stmt = $pdo->query("SELECT id, username FROM users");
    $users = $stmt->fetchAll();
    
    foreach ($users as $user) {
        $invite_code = strtoupper(substr(md5($user['username'] . time()), 0, 8));
        $update_stmt = $pdo->prepare("UPDATE users SET invite_code = ? WHERE id = ?");
        $update_stmt->execute([$invite_code, $user['id']]);
    }
    
    // 4. 创建团队统计视图
    echo "创建团队统计视图...\n";
    $pdo->exec("CREATE VIEW team_stats AS
        SELECT 
            u.id as inviter_id,
            u.username as inviter_name,
            u.invite_code,
            COUNT(team.id) as team_size,
            COALESCE(SUM(team.total_commission), 0) as total_commission_earned,
            COALESCE(SUM(tc.commission_amount), 0) as total_commission_paid
        FROM users u
        LEFT JOIN users team ON team.inviter_id = u.id
        LEFT JOIN team_commissions tc ON tc.inviter_id = u.id
        GROUP BY u.id, u.username, u.invite_code");
    
    echo "团队系统设置完成！\n";
    echo "已添加的功能：\n";
    echo "- 用户邀请关系字段\n";
    echo "- 邀请码系统\n";
    echo "- 手续费分成记录表\n";
    echo "- 团队统计视图\n";
    
} catch (PDOException $e) {
    echo "设置失败: " . $e->getMessage() . "\n";
    error_log("团队系统设置失败: " . $e->getMessage());
}
?>
